﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using StackExchange.DataExplorer.Models;
using StackExchange.DataExplorer.ViewModel;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;

namespace StackExchange.DataExplorer.Helpers
{
    public class HelperTableCache
    {
        private static ConcurrentDictionary<string, Dictionary<string, HelperTable>> cache = 
            new ConcurrentDictionary<string, Dictionary<string, HelperTable>>();
        private static HelperTableCachePreferences preferences = null;
        private static Regex tableMatcher;

        public static HelperTableCachePreferences Preferences
        {
            get { return preferences;  }
            set {
                preferences = value;

                if (value != null)
                {
                    tableMatcher = new Regex(preferences.IncludePattern);
                }
            }
        }

        public static SortedSet<string> GetCachedTables()
        {
            var tables = new SortedSet<string>();

            foreach (var siteCache in cache.Values)
            {
                foreach (var tableName in siteCache.Keys)
                {
                    if (!tables.Contains(tableName))
                    {
                        tables.Add(tableName);
                    }
                }
            }

            return tables;
        }

        public static Dictionary<string, HelperTable> GetCache(Site site)
        {
            if (Preferences == null)
            {
                lock (cache)
                {
                    if (Preferences == null)
                    {
                        Refresh();
                    }
                }

                if (Preferences == null)
                {
                    return null;
                }
            }

            if (Preferences.PerSite)
            {
                Dictionary<string, HelperTable> tablesCache;

                cache.TryGetValue(site.TinyName, out tablesCache);

                return tablesCache;
            }

            return cache.First().Value;
        }

        public static string GetCacheAsJson(Site site)
        {
            var cache = GetCache(site);

            if (cache != null)
            {
                return JsonConvert.SerializeObject(
                    cache,
                    Formatting.None,
                    new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() }
                );
            }

            return "{}";
        }

        public static void Refresh()
        {
            Preferences = AppSettings.HelperTableOptions;
            IEnumerable<Site> sites;

            cache.Clear();

            if (Preferences == null)
            {
                return;
            }

            if (!Preferences.PerSite)
            {
                sites = new List<Site>{ Current.DB.Sites.First() };
            }
            else
            {
                sites = Current.DB.Sites.All();
            }

            foreach (var site in sites)
            {
                IEnumerable<TableInfo> tables = site.GetTableInfos();
                var tablesCache = new Dictionary<string, HelperTable>();

                foreach (var table in tables) {
                    if (tableMatcher.IsMatch(table.Name))
                    {
                        var columns = new List<string>();
                        var tableCache = new HelperTable();

                        columns.Add("Id");
                        table.ColumnNames.Remove("Id");

                        var label = FindBestColumn(Preferences.NameColumns, table.ColumnNames);

                        if (label != null)
                        {
                            columns.Add(label + " AS Name");
                            tableCache.NameLabel = label;

                            var tooltip = FindBestColumn(Preferences.TooltipColumns, table.ColumnNames);

                            if (tooltip != null)
                            {
                                columns.Add(tooltip + " AS Tooltip");
                            }
                        }

                        using (var db = site.GetOpenConnection()) {
                            tableCache.Rows = db.Query<HelperTableRow>(string.Format(
                                String.Format("SELECT {0} FROM {1} ORDER BY Id ASC", columns.Join(), table.Name)
                            ));
                            tablesCache[table.Name] = tableCache;
                        }
                    }
                }

                cache[site.TinyName] = tablesCache;
            }
        }

        private static string FindBestColumn(IEnumerable<string> search, List<string> available)
        {
            if (search == null || available.Count == 0)
            {
                return null;
            }

            foreach (var candidate in search) {
                if (available.Remove(candidate))
                {
                    return candidate;
                }
            }

            var column = available[0];
            
            available.RemoveAt(0);

            return column;
        }
    }

    public class HelperTableCachePreferences
    {
        public HelperTableCachePreferences()
        {
            PerSite = false;
            IncludePattern = ".*Types$";
        }

        public override string ToString()
        {
            return "[PerSite = " + PerSite + ", IncludePattern = '" + IncludePattern + "', NameColumns = '" + NameColumns.Join() + "', TooltipColumns = '" + TooltipColumns.Join() + "']";
        }

        public bool PerSite { get; set; }
        public string IncludePattern { get; set; }
        public List<String> NameColumns { get; set; }
        public List<String> TooltipColumns { get; set; }
    }
}